<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>数据中心数据库操作</title>
    <link rel="icon" href="./logo.ico" type="image/x-icon"> <!-- 标签栏中的图标 -->
    <style>
        body {
            font-family: Arial, sans-serif;
            padding: 20px;
        }
        select {
            padding: 10px;
            margin: 10px;
            border: 1px solid #ccc;
            border-radius: 4px;
            font-size: 16px;
            color: black;
            background-color: white;
        }
        label {
            font-size: 18px;
        }
        button {
            padding: 10px 20px;
            margin: 10px;
            border: none;
            border-radius: 4px;
            background-color: #007BFF;
            color: white;
            font-size: 16px;
            cursor: pointer;
        }
        button.active {
            background-color: #0056b3;
        }
        #queryBtn {
            background-color: #28a745;
        }
        #queryBtn.active {
            background-color: #218838;
        }
        table {
            margin-top: 20px;
            border-collapse: collapse;
            width: 100%;
            display: none;
        }
        table, th, td {
            border: 1px solid #ddd;
        }
        th, td {
            padding: 8px;
            text-align: left;
            cursor: pointer;
        }
        th {
            background-color: #f4f4f4;
            position: relative;
        }
        .dragging {
            background-color: #e0e0e0;
        }
        .dragging-column {
            opacity: 0.5;
        }
        #pagination {
            margin-top: 20px;
            display: flex;
            align-items: center;
        }
        #pagination button, #pagination select {
            margin: 0 5px;
            padding: 5px 10px;
        }
        #pagination span {
            margin: 0 10px;
        }
    </style>
</head>
<body>

    <h1>数据中心数据库</h1>

    <div>
        <button id="localBtn">本地环境</button>
        <button id="testBtn">测试环境</button>
        <button id="prodBtn">生产环境</button>
    </div>

    <label for="machines" id="machinesLabel" style="display:none;">选择机器:</label>
    <select id="machines" style="display:none;">
        <option value="">请选择机器</option>
        <option value="collect_server">采集库服务器</option>
        <option value="clean_server">清洗库服务器</option>
        <option value="web_server">Web库服务器</option>
        <option value="log_server">日志库服务器</option>
    </select>

    <label for="databases">选择数据库:</label>
    <select id="databases">
        <option value="">请选择数据库</option>
    </select>

    <label for="tables">选择表:</label>
    <select id="tables">
        <option value="">请选择表</option>
    </select>
    
    <button id="queryBtn">查询</button>

    <table id="dataTable">
        <thead></thead>
        <tbody></tbody>
    </table>

    <div id="pagination" style="display: none;">
        <button id="prevPageBtn">上一页</button>
        <span id="currentPage">1</span>
        <button id="nextPageBtn">下一页</button>
        <label for="itemsPerPage">每页显示:</label>
        <select id="itemsPerPage">
            <option value="5">5</option>
            <option value="10">10</option>
            <option value="20">20</option>
            <option value="50">50</option>
            <option value="100">100</option>
        </select>
    </div>

    <script>
        const databasesSelect = document.getElementById('databases');
        const tablesSelect = document.getElementById('tables');
        const queryBtn = document.getElementById('queryBtn');
        const localBtn = document.getElementById('localBtn');
        const testBtn = document.getElementById('testBtn');
        const prodBtn = document.getElementById('prodBtn');
        const machinesSelect = document.getElementById('machines');
        const machinesLabel = document.getElementById('machinesLabel');
        const dataTable = document.getElementById('dataTable');
        const tableHead = dataTable.querySelector('thead');
        const tableBody = dataTable.querySelector('tbody');
        const prevPageBtn = document.getElementById('prevPageBtn');
        const nextPageBtn = document.getElementById('nextPageBtn');
        const currentPageSpan = document.getElementById('currentPage');
        const itemsPerPageSelect = document.getElementById('itemsPerPage');
        let selectedEnvironment = localStorage.getItem('selectedEnvironment') || '';
        let currentPage = 1;
        let itemsPerPage = parseInt(itemsPerPageSelect.value);
        let columnOrder = []; // Track the order of columns

        itemsPerPageSelect.addEventListener('change', () => {
            itemsPerPage = parseInt(itemsPerPageSelect.value);
            currentPage = 1; // Reset to first page
            queryData();
        });

        prevPageBtn.addEventListener('click', () => {
            if (currentPage > 1) {
                currentPage--;
                queryData();
            }
        });

        nextPageBtn.addEventListener('click', () => {
            currentPage++;
            queryData();
        });

        queryBtn.addEventListener('click', () => {
            currentPage = 1; // Reset to first page
            queryData();
        });

        function updateEnvironmentButtons() {
            localBtn.classList.remove('active');
            testBtn.classList.remove('active');
            prodBtn.classList.remove('active');
            if (selectedEnvironment === 'local') {
                localBtn.classList.add('active');
            } else if (selectedEnvironment === 'test') {
                testBtn.classList.add('active');
            } else if (selectedEnvironment === 'prod') {
                prodBtn.classList.add('active');
            }
        }

        localBtn.addEventListener('click', () => selectEnvironment('local', localBtn));
        testBtn.addEventListener('click', () => selectEnvironment('test', testBtn));
        prodBtn.addEventListener('click', () => selectEnvironment('prod', prodBtn));

        function selectEnvironment(env, button) {
            selectedEnvironment = env;
            localStorage.setItem('selectedEnvironment', env);
            updateEnvironmentButtons();
            loadDatabases();
            if (env === 'prod') {
                machinesSelect.style.display = 'block';
                machinesLabel.style.display = 'block';
            } else {
                machinesSelect.style.display = 'none';
                machinesLabel.style.display = 'none';
            }
        }

        async function loadDatabases() {
            if (!selectedEnvironment) {
                alert('请先选择环境');
                return;
            }

            if (selectedEnvironment === 'prod' && !machinesSelect.value) {
                alert('请先选择机器');
                return;
            }

            try {
                const envParam = selectedEnvironment === 'prod' ? `${selectedEnvironment}&machine=${machinesSelect.value}` : selectedEnvironment;
                const response = await fetch(`http://localhost:5000/show_databases?env=${envParam}`);
                if (!response.ok) {
                    throw new Error('Network response was not ok');
                }
                const result = await response.json();
                if (result.code === 0) {
                    const databases = result.databases;
                    databasesSelect.innerHTML = '<option value="">请选择数据库</option>';
                    databases.forEach(db => {
                        const option = document.createElement('option');
                        option.value = db;
                        option.textContent = db;
                        databasesSelect.appendChild(option);
                    });
                } else {
                    console.error('Error loading databases:', result.msg);
                }
                tablesSelect.innerHTML = '<option value="">请选择表</option>';
            } catch (error) {
                console.error('Error loading databases:', error);
            }
        }

        async function loadTables(dbId) {
            if (!selectedEnvironment || !dbId) {
                alert('请先选择环境和数据库');
                return;
            }
            try {
                const envParam = selectedEnvironment === 'prod' ? `${selectedEnvironment}&machine=${machinesSelect.value}` : selectedEnvironment;
                const response = await fetch(`http://localhost:5000/show_tables?env=${envParam}&db_id=${dbId}`);
                if (!response.ok) {
                    throw new Error('Network response was not ok');
                }
                const result = await response.json();
                if (result.code === 0) {
                    const tables = result.tables;
                    tablesSelect.innerHTML = '<option value="">请选择表</option>';
                    tables.forEach(table => {
                        const option = document.createElement('option');
                        option.value = table;
                        option.textContent = table;
                        tablesSelect.appendChild(option);
                    });
                } else {
                    console.error('Error loading tables:', result.msg);
                }
            } catch (error) {
                console.error('Error loading tables:', error);
            }
        }

        databasesSelect.addEventListener('change', () => {
            const dbId = databasesSelect.value;
            loadTables(dbId);
        });

        machinesSelect.addEventListener('change', () => {
            loadDatabases();
        });

        async function queryData() {
            const dbId = databasesSelect.value;
            const tableName = tablesSelect.value;
            if (!selectedEnvironment || !dbId || !tableName) {
                alert('请先选择环境、数据库和表');
                return;
            }
            try {
                const envParam = selectedEnvironment === 'prod' ? `${selectedEnvironment}&machine=${machinesSelect.value}` : selectedEnvironment;
                const response = await fetch(`http://localhost:5000/query_data?env=${envParam}&db_id=${dbId}&table=${tableName}&page=${currentPage}&items_per_page=${itemsPerPage}`);
                if (!response.ok) {
                    throw new Error('Network response was not ok');
                }
                const result = await response.json();
                if (result.code === 0) {
                    const data = result.data;
                    updateTable(data);
                } else {
                    console.error('Error loading data:', result.msg);
                }
            } catch (error) {
                console.error('Error loading data:', error);
            }
        }

        function updateTable(data) {
            if (!data || data.length === 0) {
                dataTable.style.display = 'none';
                pagination.style.display = 'none';
                return;
            }

            tableHead.innerHTML = '';
            tableBody.innerHTML = '';

            const headerRow = document.createElement('tr');
            const headers = data[0];
            columnOrder = headers.slice(); // Initialize column order

            headers.forEach(header => {
                const th = document.createElement('th');
                th.textContent = header;
                th.setAttribute('draggable', true);
                th.addEventListener('dragstart', handleDragStart);
                th.addEventListener('dragover', handleDragOver);
                th.addEventListener('drop', handleDrop);
                headerRow.appendChild(th);
            });

            tableHead.appendChild(headerRow);

            data.slice(1).forEach(row => {
                const rowElement = document.createElement('tr');
                columnOrder.forEach((column, index) => {
                    const cell = document.createElement('td');
                    const cellData = row[index]; // Use index to access data
                    if (isUrl(cellData)) {
                        const a = document.createElement('a');
                        a.href = cellData;
                        a.textContent = cellData;
                        a.target = '_blank';
                        cell.appendChild(a);
                    } else {
                        cell.textContent = cellData;
                    }
                    rowElement.appendChild(cell);
                });
                tableBody.appendChild(rowElement);
            });

            dataTable.style.display = 'table';
            pagination.style.display = 'flex';
            currentPageSpan.textContent = currentPage;
        }

        let draggedColumnIndex;
        function handleDragStart(event) {
            draggedColumnIndex = [...event.target.parentNode.children].indexOf(event.target);
            event.dataTransfer.effectAllowed = 'move';
            event.dataTransfer.setData('text/html', event.target.outerHTML);
            event.target.classList.add('dragging-column');
        }

        function handleDragOver(event) {
            event.preventDefault();
            event.dataTransfer.dropEffect = 'move';
        }

        function handleDrop(event) {
            event.stopPropagation();
            const targetColumnIndex = [...event.target.parentNode.children].indexOf(event.target);
            if (draggedColumnIndex !== targetColumnIndex) {
                reorderColumns(draggedColumnIndex, targetColumnIndex);
            }
            document.querySelectorAll('.dragging-column').forEach(el => el.classList.remove('dragging-column'));
        }

        function reorderColumns(fromIndex, toIndex) {
            const headers = Array.from(tableHead.querySelectorAll('th'));
            const rows = Array.from(tableBody.querySelectorAll('tr'));

            headers[fromIndex].parentNode.insertBefore(headers[fromIndex], headers[toIndex]);
            rows.forEach(row => {
                const cells = Array.from(row.querySelectorAll('td'));
                row.insertBefore(cells[fromIndex], cells[toIndex]);
            });

            const temp = columnOrder[fromIndex];
            columnOrder[fromIndex] = columnOrder[toIndex];
            columnOrder[toIndex] = temp;
        }

        function isUrl(str) {
            try {
                new URL(str);
                return true;
            } catch {
                return false;
            }
        }

        updateEnvironmentButtons();
        if (selectedEnvironment) {
            loadDatabases();
        }
    </script>
</body>
</html>
